from flask import Flask, jsonify, request
from flask_cors import CORS
import pymysql

app = Flask(__name__)
CORS(app)  # 解决跨域问题

# 数据库配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'Lantk0000*',
    'database': 'demo_db'
}

# 获取数据库连接
def get_db_connection():
    return pymysql.connect(**db_config)

# 创建数据表（首次运行需要）
# @app.before_first_request
# def init_db():
#     conn = get_db_connection()
#     try:
#         with conn.cursor() as cursor:
#             cursor.execute('''
#                 CREATE TABLE IF NOT EXISTS items (
#                     id INT AUTO_INCREMENT PRIMARY KEY,
#                     name VARCHAR(100) NOT NULL,
#                     description TEXT
#                 )
#             ''')
#         conn.commit()
#     finally:
#         conn.close()

# 读取数据接口
@app.route('/api/items', methods=['GET'])
def get_items():
    conn = get_db_connection()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute("SELECT * FROM items")
            items = cursor.fetchall()
        return jsonify(items)
    finally:
        conn.close()

# 写入数据接口
@app.route('/api/items', methods=['POST'])
def add_item():
    data = request.get_json()
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = "INSERT INTO items (name, description) VALUES (%s, %s)"
            cursor.execute(sql, (data['name'], data['description']))
        conn.commit()
        return jsonify({'status': 'success'}), 201
    finally:
        conn.close()

# 新增获取单个项目接口
@app.route('/api/items/<int:item_id>', methods=['GET'])
def get_item(item_id):
    conn = get_db_connection()
    try:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute("SELECT * FROM items WHERE id=%s", (item_id,))
            item = cursor.fetchone()
            if not item:
                return jsonify({'error': 'Item not found'}), 404
            return jsonify(item)
    except pymysql.Error as e:
        return jsonify({'error': 'Database error', 'details': str(e)}), 500
    finally:
        conn.close()

# 更新数据接口
@app.route('/api/items/<int:item_id>', methods=['PUT'])
def update_item(item_id):
    data = request.get_json()
    # 添加参数校验
    if 'name' not in data or 'description' not in data:
        return jsonify({'error': 'Missing required fields'}), 400
    
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = "UPDATE items SET name=%s, description=%s WHERE id=%s"
            affected_rows = cursor.execute(sql, (data['name'], data['description'], item_id))
            
            # 检查是否成功更新
            if affected_rows == 0:
                conn.rollback()
                return jsonify({'error': 'Item not found'}), 404
                
            # 获取更新后的数据
            cursor.execute("SELECT * FROM items WHERE id=%s", (item_id,))
            updated_item = cursor.fetchone()
            
        conn.commit()
        return jsonify(updated_item)  # 返回完整数据而不是仅状态
    except pymysql.Error as e:
        conn.rollback()
        return jsonify({'error': 'Database error', 'details': str(e)}), 500
    finally:
        conn.close()

# 删除数据接口
@app.route('/api/items/<int:item_id>', methods=['DELETE'])
def delete_item(item_id):
    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = "DELETE FROM items WHERE id=%s"
            cursor.execute(sql, (item_id,))
        conn.commit()
        return jsonify({'status': 'success'})
    finally:
        conn.close()


if __name__ == '__main__':
    app.run(debug=True)
